package org.feng;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.util.WorkBookUtil;
import com.alibaba.excel.write.handler.AbstractWorkbookWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * TODO
 *
 * @author 01434188
 */
@Controller
@RequestMapping("/excelTemplate2")
public class ExcelTemplateDemo2Controller {

    @GetMapping("/erpFee")
    public String erpFee(HttpServletResponse response) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        InputStream template;
        try {
            template = new ClassPathResource("detail_invoice_template.xlsx").getInputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        List<DetailInvoice> exportList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList.add(detailInvoice);
        }

        List<DetailInvoice> exportList2 = new ArrayList<>();
        for (int i = 6; i < 10; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList2.add(detailInvoice);
        }
        List<DetailInvoice> exportList3 = new ArrayList<>();
        for (int i = 11; i < 15; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList3.add(detailInvoice);
        }

        ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(template).build();

        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        writeSheet.setSheetName("sheet1");
        excelWriter.fill(exportList, writeSheet);
        Map<String, String> totalMap = new HashMap<>();
        totalMap.put("invoiceNo", "我是发票号");
        excelWriter.fill(totalMap, writeSheet);



        WriteSheet writeSheet2 = EasyExcel.writerSheet().build();
        writeSheet2.setSheetName("sheet2");
        excelWriter.fill(exportList2, writeSheet2);
        Map<String, String> totalMap2 = new HashMap<>();
        totalMap2.put("invoiceNo", "我是发票号2");
        excelWriter.fill(totalMap2, writeSheet2);

        WriteSheet writeSheet3 = EasyExcel.writerSheet().build();

        writeSheet3.setSheetName("sheet3");
        excelWriter.fill(exportList3, writeSheet3);
        Map<String, String> totalMap3 = new HashMap<>();
        totalMap3.put("invoiceNo", "我是发票号3");
        excelWriter.fill(totalMap3, writeSheet3);


        excelWriter.finish();

        // 转码
        String filename = URLEncoder.encode("fee费用" + System.currentTimeMillis() + ".xlsx", "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        response.setContentType("application/x-msdownload");
        response.setCharacterEncoding("utf-8");
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(out.toByteArray());
        outputStream.flush();
        return "";
    }


    @GetMapping("/erpFeezip")
    public String erpFeeZip(HttpServletResponse response) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        InputStream template;
        try {
            template = new ClassPathResource("detail_invoice_template.xlsx").getInputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        List<DetailInvoice> exportList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList.add(detailInvoice);
        }

        List<DetailInvoice> exportList2 = new ArrayList<>();
        for (int i = 6; i < 10; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList2.add(detailInvoice);
        }
        List<DetailInvoice> exportList3 = new ArrayList<>();
        for (int i = 11; i < 15; i++) {
            DetailInvoice detailInvoice = new DetailInvoice();
            detailInvoice.setSettleOrderNo("结算单号"+ i);
            detailInvoice.setStartAddr("起运地" + i);
            detailInvoice.setEndAddr("到达地" + i);
            detailInvoice.setTransMode("运输方式" + i);
            detailInvoice.setCarNo("车牌号/车皮号/船号" + i);
            detailInvoice.setMaterialName("物料名称" + i);
            exportList3.add(detailInvoice);
        }

        ExcelWriter excelWriter = EasyExcel.write(out)
                .registerWriteHandler(new DeleteTemplateSheetWriteHandler(4)).withTemplate(template).build();

        WriteSheet writeSheet = EasyExcel.writerSheet()
                .build();

        writeSheet.setSheetName("sheet1");
        excelWriter.fill(exportList, writeSheet);
        Map<String, String> totalMap = new HashMap<>();
        totalMap.put("invoiceNo", "我是发票号");
        excelWriter.fill(totalMap, writeSheet);



        WriteSheet writeSheet2 = EasyExcel.writerSheet().build();
        writeSheet2.setSheetName("sheet2");
        excelWriter.fill(exportList2, writeSheet2);
        Map<String, String> totalMap2 = new HashMap<>();
        totalMap2.put("invoiceNo", "我是发票号2");
        excelWriter.fill(totalMap2, writeSheet2);

        WriteSheet writeSheet3 = EasyExcel.writerSheet().build();

        writeSheet3.setSheetName("sheet3");
        excelWriter.fill(exportList3, writeSheet3);
        Map<String, String> totalMap3 = new HashMap<>();
        totalMap3.put("invoiceNo", "我是发票号3");
        excelWriter.fill(totalMap3, writeSheet3);


        excelWriter.finish();

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ZipOutputStream zipOut = new ZipOutputStream(byteArrayOutputStream);
        addToZipFile("test.xlsx", zipOut, out);
        zipOut.flush();
        zipOut.close();
        // 指定文件名
        response.setHeader("Content-disposition", "attachment;filename=" + "filezid1.zip");
        response.setContentType("application/x-msdownload");
        response.setCharacterEncoding("utf-8");
        response.getOutputStream().write(byteArrayOutputStream.toByteArray());
        response.getOutputStream().flush();


        return "";
    }



    public static class DeleteTemplateSheetWriteHandler extends AbstractWorkbookWriteHandler {
        private static final int FIRST_SHEET_INDEX = 0;

        private final int removeFrom;

        public DeleteTemplateSheetWriteHandler(int removeFrom) {
            this.removeFrom = removeFrom;
        }

        @Override
        public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // 总的sheet数量
            int numberOfSheets = workbook.getNumberOfSheets();
            System.out.println("numberOfSheets ==>" + numberOfSheets);

            // 获取需要删除的sheet名
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            List<String> removeSheetNames = new ArrayList<>();
            int index = 1;
            while (sheetIterator.hasNext()) {
                Sheet next = sheetIterator.next();
                String sheetName = next.getSheetName();
                System.out.println("sheet====" + sheetName);
                if(index >= removeFrom) {
                    removeSheetNames.add(sheetName);
                    System.out.println(sheetName);
                }
                index++;
            }

            System.out.println("开始删除sheet");
            // 删除指定的sheet
            if (!CollectionUtils.isEmpty(removeSheetNames)) {
                for (String removeSheetName : removeSheetNames) {
                    int sheetIndex = workbook.getSheetIndex(removeSheetName);
                    System.out.println("删除sheet"+sheetIndex + ", sheetName=" + removeSheetName);
                    workbook.removeSheetAt(sheetIndex);
                }
            }


            // 指定工作簿打开的sheet页
            workbook.setActiveSheet(FIRST_SHEET_INDEX);
        }
    }

    @GetMapping("/erpFeezip1")
    public String erpFeeZip1(HttpServletResponse response) throws IOException {

        InputStream template;
        try {
            template = new ClassPathResource("detail_invoice_template.xlsx").getInputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ZipOutputStream zipOut = new ZipOutputStream(byteArrayOutputStream);


        // 读文件
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        byte[] bytes = new byte[1024];
        int length;
        while ((length = template.read(bytes)) >= 0) {
            out.write(bytes, 0, length);
        }

        addToZipFile("test.xlsx", zipOut, out);
        addToZipFile("test1.xlsx", zipOut, out);

        zipOut.flush();
        zipOut.close();
        // 指定文件名
        response.setHeader("Content-disposition", "attachment;filename=" + "filezid1.zip");
        response.setContentType("application/x-msdownload");
        response.setCharacterEncoding("utf-8");
        response.getOutputStream().write(byteArrayOutputStream.toByteArray());
        response.getOutputStream().flush();

        return "";
    }


    @Data
    public static class DetailInvoice {
        private String settleOrderNo;
        private String startAddr;
        private String endAddr;
        private String transMode;
        private String carNo;
        private String materialName;
    }

    public static void addToZipFile(String filename, ZipOutputStream zipOutputStream, ByteArrayOutputStream outData) throws IOException {
        // 创建ZipEntry对象并设置文件名
        ZipEntry zipEntry = new ZipEntry(filename);
        zipOutputStream.putNextEntry(zipEntry);
        // 数据流写入zip
        outData.writeTo(zipOutputStream);
        zipOutputStream.closeEntry();
    }
}
